use master
GO
--Drop/Create Database
drop database CoffeeSoft
GO
create database CoffeeSoft
on(Name='CoffeeSoft_data' ,fileName='D:\Dulieu\DataSokul\CoffeeSoft.mdf')
log on(Name='CoffeeSoft_log' ,fileName='D:\Dulieu\DataSokul\CoffeeSoft.ldf')
GO
USE CoffeeSoft
GO
--Create Table

CREATE table Area
(
	AreaId VARCHAR (20),
	AreaName NVARCHAR(50) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Area primary key(AreaId)
)
go

CREATE table KulExchange
(
	KulId VARCHAR (20) not null primary key,
	KulName NVARCHAR(50) not null,
	KulDescription NVARCHAR(200) not null,
	Value int not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
go


CREATE table MemberType
(
	MemberTypeId VARCHAR (20) not null primary key,
	MemberTypeName NVARCHAR(50) not null,
	MemberTypeDescription NVARCHAR(200) not null,
	KulCoinToUpgrade int not null,
	DiscountForThisType int not null,

	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	
)
go


CREATE table [State]
(
	StateId varchar(20) not null primary key,
	StateDescription NVARCHAR(200) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	
)
go
CREATE table MemberCard
(
	MemberCardId int identity(1,1) not null primary key,
	MemberCardNumber VARCHAR(50) unique not null,
	StateId varchar(20) not null,

	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,

	constraint FK_State_Membercard foreign key(StateId)
	references State(StateId)
	
)


go
--Create table Customer
create table Customer
(
	CustomerId int identity(1,1) primary key,
	FirstName nvarchar(50) not null,
	LastName nvarchar(50) not null,
	MiddleName nvarchar(50) not null,
	FullName nvarchar(200) ,
	Dob datetime,
	IdCard varchar(20) not null,
	[Address] nvarchar(200),
	Phone varchar(11),
	Mobile varchar(11),

	IsActiveCard bit not null, 
	DateActiveCard datetime,

	KulPoint int not null,
	TotalKulPoint int not null,
	MemberTypeId varchar(20) not null,
	MemberCardNumber VARCHAR(50),
	TotalLost int not null,
	IsCustomerLock bit not null,

	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,
	constraint FK_Customer_MemberType foreign key(MemberTypeId)
	references MemberType(MemberTypeId)
)
go

--Create table Customer history
create table CustomerHistory
(
	CustomerHistoryId int identity(1,1) not null primary key,
	CustomerId int not null,
	FunctionName varchar(100) not null,
	Detail nvarchar (200) not null,

	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,
	constraint FK_Customer_customerhistory foreign key(CustomerId)
	references Customer(CustomerId)
)

go

--Create table ExportKulExchange
create table ExportKulExchange
(
	ExportKulExchangeId int identity(1,1) not null primary key,
	[From] decimal not null,
	[To] decimal not null,
	KulValue int not null,

	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	
)

go
--Ban
create table  [Table]
(
	TableId INT IdENTITY(1,1) not null,
	TableName NVARCHAR(50)not null,
	AreaId VARCHAR (20) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime

	constraint PK_TABLE primary key(TableId),
	constraint FK_Area_TABLE foreign key(AreaId)
	references Area(AreaId)
	on Update cascade
)
GO

create table Department
(
	DepartmentId int identity(1,1)not null,
	DepartmentName nvarchar(100)not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Department primary key(DepartmentId)
)
go
--create table WareHouseType
create table WareHouseType
(
	WareHouseTypeId int identity(1,1) primary key not null,
	WareHouseTypeName nvarchar(100),
	WareHouseTypeDescription nvarchar(200),
		DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
go
--create table WareHouse
create table WareHouse
(
	WareHouseId int identity(1,1) not null primary key,
	WareHouseName nvarchar(100) not null,
	WareHouseDescription nvarchar(200),
	WareHouseTypeId int not null,
	IsSale bit not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint FK_WareHouse_WareHouseType foreign key(WareHouseTypeId)
	references WareHouseType(WareHouseTypeId)
)
go

--BacLuong
create table Wage
(
	WageId INT IdENTITY(1,1) not null,
	WageName nvarchar(50) not null,
	Price float,
	DepartmentId int  not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Wage primary key(WageId,DepartmentId),
	constraint FK_Wage_Department foreign key (DepartmentId)
	references Department(DepartmentId)
	on Update cascade
)
go

--Nhanvien
create table Employer
(
	EmployerId int Identity(1,1) not null,
	FirstName NVARCHAR(50) NOT NULL,
	MiddleName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	FullName NVARCHAR(150),
	Dob DateTIME NOT NULL,
	IdCard varchar(9)not null,
	[Address] nvarchar(100),
	DateWork Datetime not null,
	DepartmentId int  not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_EmployerId primary key(EmployerId),
	constraint FK_EmployerId_Department foreign KEY (DepartmentId)
	references Department(DepartmentId)
	on Update cascade
)
GO
CREATE TABLE [GroupRole]
(
	GroupRoleId VARCHAR(50) PRIMARY KEY NOT NULL,
	GroupRoleName NVARCHAR(100) NOT NULL,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
GO

CREATE TABLE [Role]
(
	RoleId VARCHAR(50) PRIMARY KEY NOT NULL,
	RoleName NVARCHAR(100) NOT NULL,
	GroupRoleId VARCHAR(50) NOT NULL,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	CONSTRAINT FK_Role_GroupRole FOREIGN KEY(GroupRoleId)
	REFERENCES GroupRole(GroupRoleId)
)
GO

CREATE TABLE GroupUser
(
	GroupUserId INT IdENTITY(1,1) NOT NULL PRIMARY KEY,
	GroupUserName NVARCHAR(100) NOT NULL,
	GroupUserDESCRIPTION NVARCHAR(100),
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
GO
CREATE TABLE Detail_GroupUserRole
(
	GroupUserId INT NOT NULL,
	RoleId VARCHAR(50) NOT NULL,
	Permit BIT 
	CONSTRAINT PK_Detail_GroupUser_Role PRIMARY KEY(GroupUserId,RoleId),
	CONSTRAINT FK_Detail_GroupUser_Role_GroupUser FOREIGN KEY(GroupUserId)
	REFERENCES GroupUser(GroupUserId),
	CONSTRAINT FK_Detail_GroupUser_Role_ROLE FOREIGN KEY(RoleId)
	REFERENCES [Role](RoleId)
)
GO
CREATE TABLE UserAccount
(
	UserName VARCHAR(30) NOT NULL PRIMARY KEY ,
	[Password] VARCHAR(32) NOT NULL,
	FirstName NVARCHAR(50) NOT NULL,
	MiddleName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	FullName NVARCHAR(150),
	Dob DateTIME NOT NULL,
	Email VARCHAR(100) NOT NULL,
	IdCard VARCHAR(20) NOT NULL,
	DateCreate DateTIME NOT NULL,
	LastUpdate DateTIME,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	IsAdmin BIT NOT NULL,
	MobilePhone VARCHAR(20),
	HomePhone VARCHAR(20),
	GroupUserId INT NOT NULL,
	EmployerId int 
	CONSTRAINT FK_UserAccount_GroupUser FOREIGN KEY(GroupUserId)
	REFERENCES GroupUser(GroupUserId)
	
)
GO
create table Company
(
	CompanyId int identity(1,1) primary key,
	CompanyName nvarchar(200) not null,
	CompanyAddress nvarchar(200)not null,
	Phone varchar(11),
	Mobile varchar(11),
	Fax varchar(50),
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
go

--Donvi
CREATE TABLE Unit
(
	UnitId int Identity(1,1) not null,
	UnitName nvarchar(50) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Unit primary key(UnitId)
)
go
--nguyenLieu
CREATE TABLE Material
(
	MaterialId int Identity(1,1) not null,
	MaterialName nvarchar(50) not null,
	UnitId int not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Material primary key(MaterialId)
	constraint FK_Material foreign key(UnitId)
	references Unit(UnitId)
)
go
CREATE TABLE ItemType
(
	ItemTypeId int Identity(1,1)not null,
	ItemTypeName nvarchar(50),
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_PhanLoaiSanPham primary key(ItemTypeId)
)
go
--Sanpham
CREATE TABLE Item 
(
	ItemId int Identity(1,1) not null,
	UnitId int not null,
	ItemName nvarchar(50),
	ItemTypeId int not null,
	IsDrink bit not null,
	--Price float not null,
	Shortcut VARCHAR(50),
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
	constraint PK_Item primary key(ItemId),
	constraint FK_Item_ItemType foreign key(ItemTypeId)
	references ItemType(ItemTypeId)
	on Update cascade,
	constraint FK_Item_Unit foreign key(UnitId)
	references Unit(UnitId)
)
go
-- create table ItemPrice
create table ItemPrice
(
	ItemPriceId int identity(1,1)primary key,
	ItemId int not null,
	IsCurrentPrice bit not null,
	Value decimal(18,2) not null,
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30)
	constraint FK_ItemPrice_Item foreign key(ItemId)
	references Item(ItemId)
)
go
--create table DetailItemWareHouse
create table DetailItemWareHouse
(
	DetailItemWareHouseId int identity(1,1) primary key,
	ItemId int not null,
	WareHouseId int not null,
	Quantity int not null

	constraint FK_DetailItemWareHouse_Item foreign key(ItemId)
	references Item(ItemId),
	constraint FK_DetailItemWareHouse_WareHouse foreign key(WareHouseId)
	references WareHouse(WareHouseId)
	


)
go


--HoaDonNhap
CREATE TABLE Import
(
	ImportId varchar(10) not null,
	DateImport Datetime not null,
	Note nvarchar(500),
	UserName varchar(30)  not null
	constraint PK_Import primary key(ImportId),
	constraint FK_Import_UserAccount foreign key(UserName)
	references UserAccount(UserName)
)
go
--CThoadonnhap
CREATE TABLE ImportDetail
(
	ImportId varchar(10) not null,
	MaterialId int not null,
	Quantity float not null,
	Price float not null,
	UnitId int not null
	constraint PK_ImportDetail primary key(ImportId,MaterialId),
	constraint FK_ImportDetail_Import foreign key(ImportId)
	references Import(ImportId),
	constraint FK_ImportDetail_Material foreign key(MaterialId)
	references Material(MaterialId)

)
go
--Hoadonxuat
CREATE TABLE Export
(
	ExportId varchar(10) not null,
	TableId INT not null,
	UserCreate varchar(30) not null,
	TableDiscount decimal(18,2) not null,
	DateCreate DateTIME,
	IsPayAll bit,
	Vat decimal(18,2) not null,
	[Service] decimal(18,2) not null,
	DatePaid DateTIME,
	 KulQuantity int not null,
	 MoneyExchangeByKul decimal not null,
	constraint PK_Export primary key(ExportId),
	constraint FK_Export_TABLE foreign key(TableId)
	references [Table](TableId),
	constraint FK_Export_UserAccount1 foreign key(UserCreate)
	references UserAccount(UserName)
)
go
--CThoadonxuat
create table ExportDetail
(
	ExportId varchar(10) not null,
	ItemId int not null,
	Quantity int not null,
	Price decimal(18,2) not null,
	ItemDiscount decimal(18,2) not null,
	IsItemPay bit,
	ItemPriceId int,
	Note bit
	constraint PK_ExportDetail primary key(ExportId,ItemId),
	constraint FK_ExportDetail_Item foreign key(ItemId)
	references Item(ItemId),
	constraint FK_ExportDetail_Export foreign key(ExportId)
	references Export(ExportId),
	constraint FK_ExportDetail_ItemPrice foreign key(ItemPriceId)
	references ItemPrice(ItemPriceId),
)
go

CREATE table GiftType
(
	GiftTypeId int identity(1,1) not null primary key,
	GiftName NVARCHAR(50) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime
)
go

CREATE table Gift
(
	GiftId int identity(1,1) not null primary key,
	GiftName NVARCHAR(50) not null,
	GiftDescription NVARCHAR(200) not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,
	GiftTypeId int not null
	constraint PK_Gift_Type foreign key(GiftTypeId)
	references GiftType(GiftTypeId)
)
go

CREATE table GiftStore
(
	GiftId int not null,
	PriceImportVnd decimal not null,
	PriceExportVnd decimal not null,
	PriceExportKul decimal not null,
	Quantity int not null,
	DateCreate datetime not null,
	LastUpdate datetime,
	UpdateBy varchar(30),
	UserCreate varchar(30),
	EffectiveFromDate datetime not null,
	EffectiveToDate datetime,
	constraint PK_GiftStore primary key(GiftId,PriceImportVnd),
	constraint PK_Gift_GiftStore foreign key(GiftId)
	references Gift(GiftId)
)
go
CREATE TABLE ImportGift
(
	ImportGiftId varchar(10) not null,
	DateImport Datetime not null,
	Note nvarchar(500),
	UserName varchar(30)  not null
	constraint PK_ImportGift primary key(ImportGiftId),
	constraint FK_ImportGift_UserAccount foreign key(UserName)
	references UserAccount(UserName)
)
go
CREATE TABLE ExportGift
(
	ExportGiftId varchar(10) not null,
	MemberCardNumber varchar(50) ,
	UserCreate varchar(30) not null,
	DateCreate DateTIME,
	IsMember bit 
	constraint PK_ExportGift primary key(ExportGiftId),
	constraint FK_ExportGift_UserAccount1 foreign key(UserCreate)
	references UserAccount(UserName)
)
go
CREATE TABLE ImportGiftDetail
(
	ImportGiftId varchar(10) not null,
	GiftId int not null,
	PriceImportVnd decimal not null,
	PriceExportVnd decimal not null,
	PriceExportKul decimal not null,
	Quantity int not null,
	constraint PK_ImportGiftGiftDetail primary key(ImportGiftId,GiftId, PriceImportVnd),
	constraint FK_ImportGiftGiftDetail_ImportGift foreign key(ImportGiftId)
	references ImportGift(ImportGiftId),
	constraint FK_ImportGiftGiftDetail_Material foreign key(GiftId)
	references Gift(GiftId)

)
go

create table ExportGiftDetail
(
	ExportGiftId varchar(10) not null,
	GiftId int not null,
	Quantity int not null,
	PriceVnd decimal(18,2) not null,
    PriceKul decimal(18,2) not null,
	constraint PK_ExportGiftDetail primary key(ExportGiftId,GiftId),
	constraint FK_ExportGiftDetail_Item foreign key(GiftId)
	references Gift(GiftId),
	constraint FK_ExportGiftDetail_Export foreign key(ExportGiftId)
	references ExportGift(ExportGiftId)
)

go

-- insert username and groupuser for login


-- Group Users
insert into GroupUser(GroupUserName,GroupUserDescription, DateCreate,LastUpdate,UpdateBy,UserCreate,EffectiveFromDate)
			   values('Administrators',N'Group này chứa những tài khoản có quyền quan lý',getdate(),getdate(),'','',getdate())
go
insert into GroupUser(GroupUserName,GroupUserDescription, DateCreate,LastUpdate,UpdateBy,UserCreate,EffectiveFromDate)
			   values('Users',N'Group này chứa những tài khoản khác',getdate(),getdate(),'','',getdate())

go
insert into GroupUser(GroupUserName,GroupUserDescription, DateCreate,LastUpdate,UpdateBy,UserCreate,EffectiveFromDate)
			   values('Admins',N'Group này chứa những tài khoản có quyền quan lý',getdate(),getdate(),'','',getdate())
-- UserAccount
go 
insert into UserAccount(UserName,[Password],FirstName,MiddleName,LastName,FullName,Dob,Email,IdCard,DateCreate,LastUpdate,UpdateBy,UserCreate,IsAdmin,MobilePhone,HomePhone,GroupUserId,EmployerId)
			     values('administrator','bfe96f8a558d7a65fbead98c07b18895',N'Hoàng',N'Minh',N'Nguyễn',N'Nguyễn Minh Hoàng','12/12/2013','hoangsoft35@gmail.com','321319250',GETDATE(),GETDATE(),'','',1,'01229952221','0753875471',1,'')

go
insert into UserAccount(UserName,[Password],FirstName,MiddleName,LastName,FullName,Dob,Email,IdCard,DateCreate,LastUpdate,UpdateBy,UserCreate,IsAdmin,MobilePhone,HomePhone,GroupUserId,EmployerId)
			     values('user','bfe96f8a558d7a65fbead98c07b18895',N'Hoàng',N'Minh',N'Nguyễn',N'Nguyễn Minh Hoàng','12/12/2013','hoangsoft35@gmail.com','321319250',GETDATE(),GETDATE(),'','',0,'01229952221','0753875471',2,'')

insert into UserAccount(UserName,[Password],FirstName,MiddleName,LastName,FullName,Dob,Email,IdCard,DateCreate,LastUpdate,UpdateBy,UserCreate,IsAdmin,MobilePhone,HomePhone,GroupUserId,EmployerId)
			     values('admin','bfe96f8a558d7a65fbead98c07b18895',N'Hoàng',N'Minh',N'Nguyễn',N'Nguyễn Minh Hoàng','12/12/2013','hoangsoft35@gmail.com','321319250',GETDATE(),GETDATE(),'','',1,'01229952221','0753875471',3,'')


-- Store Procedure
go
create PROC [dbo].[AddItemToTable](@EXPORTID VARCHAR(10),@ITEMID INT,@QUANTITY INT)
AS
DECLARE @PRICE FLOAT
DECLARE @PRICEID INT
DECLARE @ISITEMIDEXIST VARCHAR
DECLARE @ISDELETE BIT
DECLARE @ITEMCHECK FLOAT
	BEGIN
	
	SET  @PRICE= (SELECT top 1 Value FROM Item,ItemPrice WHERE Item.ItemId=@ITEMID and Item.ItemId = ItemPrice.ItemId order by ItemPrice.EffectiveFromDate desc)
	if @PRICE IS Null   
		select @PRICE =0
	SET @ISITEMIDEXIST= (SELECT COUNT(ITEMID) FROM EXPORTDETAIL WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID)
	IF(@ISITEMIDEXIST>0)
		UPDATE EXPORTDETAIL SET QUANTITY=(QUANTITY+@QUANTITY) WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID
	ELSE
		INSERT INTO EXPORTDETAIL(EXPORTID,ITEMID,QUANTITY,PRICE,ITEMDISCOUNT,ISITEMPAY)
			VALUES(@EXPORTID,@ITEMID,@QUANTITY,@PRICE,0,0)
	
	SET @ITEMCHECK=(SELECT QUANTITY FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID AND ITEMID=@ITEMID)
	IF (@ITEMCHECK=NULL)
	BEGIN
		SET @ITEMCHECK=0
		END
	IF(@ITEMCHECK<=0)
		BEGIN
			 DELETE FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID AND ITEMID=@ITEMID
		 END
	 IF((SELECT COUNT(ITEMID) FROM EXPORTDETAIL WHERE  EXPORTID=@EXPORTID)=0)
		 BEGIN
			 DELETE FROM EXPORTDETAIL WHERE EXPORTID=@EXPORTID
			 DELETE FROM EXPORT  WHERE EXPORTID=@EXPORTID
			 SET @ISDELETE=1
		 END
	 ELSE 
		SET @ISDELETE=0

	SELECT @ISDELETE
	END

	
go

	create PROC [dbo].[GetTableNotHaveGuestByAreaId](@AREAID VARCHAR(20))
AS
	BEGIN
		SELECT [TABLE].TABLEID, TABLENAME ,AREA.AREAID
		FROM [TABLE],AREA
		WHERE [TABLE].TABLEID=[TABLE].TABLEID
		AND AREA.AREAID = [TABLE].AREAID
		AND AREA.AREAID =@AREAID
		AND [TABLE].[TABLEID] NOT IN (SELECT TABLEID  FROM EXPORT WHERE ISPAYALL = 0)
	END


	go

	
create PROC [dbo].[GETNUMBEROFEXPORT](@TABLEID INT,@USERNAME varchar(30))
AS
DECLARE @EXP VARCHAR(10)
DECLARE @EXP1 VARCHAR(10)='HD'
DECLARE @EXP2 VARCHAR(10)
	BEGIN
		SELECT @EXP= CAST((CAST((SELECT TOP 1 SUBSTRING(EXPORTID,3,8)FROM EXPORT ORDER BY DATECREATE DESC ) AS INT)+1) AS varchar)
		IF @EXP IS NULL
		BEGIN
			SET @EXP='01'
		END
		SET @EXP2=@EXP1+@EXP
		INSERT INTO EXPORT([EXPORTID],[TABLEID],UserCreate,[TABLEDISCOUNT],[DATECREATE],[ISPAYALL],[VAT],[SERVICE],KulQuantity,MoneyExchangeByKul)
		VALUES(@EXP2,@TABLEID,@USERNAME,0,GETDATE(),0,0,0,0,0)

		SELECT @EXP2
	END


go
create PROC [dbo].[UPDATEVATSERVICEDISCOUNT](@EXPORTID VARCHAR(10),@VAT decimal,@SERVICE decimal,@DISCOUNT decimal)
AS
	BEGIN
		UPDATE EXPORT SET VAT = @VAT,[SERVICE]=@SERVICE,TABLEDISCOUNT=@DISCOUNT 
		WHERE EXPORTID=@EXPORTID
	END

go

	create PROC [dbo].[UPDATEDISCOUNTFORITEM](@EXPORTID VARCHAR(10),@ITEMID INT, @ITEMDISCOUNT decimal)
	AS
		BEGIN
			UPDATE EXPORTDETAIL SET ITEMDISCOUNT=@ITEMDISCOUNT
			WHERE EXPORTID=@EXPORTID AND ITEMID=@ITEMID
		END

go


	create PROC [dbo].[PAIDBILL](@EXPORTID VARCHAR(10), @DATEPAID DATETIME)
	AS

		BEGIN
			UPDATE EXPORT
			 SET ISPAYALL=1,DATEPAID = @DATEPAID
			 WHERE EXPORT.EXPORTID=@EXPORTID

		END

go


create PROC [dbo].[GETDETAILEXPORTBYEXPORTID](@EXPORTID VARCHAR(10))
AS
	BEGIN
		SELECT Export.KulQuantity,Export.MoneyExchangeByKul, EXPORT.EXPORTID,EXPORTDETAIL.ITEMID,QUANTITY,EXPORTDETAIL.PRICE,ITEMDISCOUNT,ISITEMPAY,ITEMNAME,TOTAL=((QUANTITY*EXPORTDETAIL.PRICE)-(QUANTITY*EXPORTDETAIL.PRICE*ITEMDISCOUNT)/100),VAT,[SERVICE],TABLEDISCOUNT
		FROM EXPORT,EXPORTDETAIL,ITEM
		WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID 
			AND ITEM.ITEMID=EXPORTDETAIL.ITEMID
			AND EXPORT.EXPORTID=@EXPORTID

	END

go


create PROC [dbo].[GETLISTEXPORT](@FROM DATE, @TO DATE, @ISPAYALL BIT)
AS
	BEGIN
		SELECT 
				EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, TOTAL=SUM(QUANTITY*PRICE),
				[SERVICE]=(SUM(QUANTITY*PRICE)*[SERVICE])/100,
				[VAT]=(SUM(QUANTITY*PRICE)*[VAT])/100,
				ITEMDISCOUNT=SUM(QUANTITY*PRICE*ITEMDISCOUNT/100*-1),
				TABLEDISCOUNT= (SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100*-1,
				FINALTOTAL= (SUM(QUANTITY*PRICE)+(SUM(QUANTITY*PRICE)*[SERVICE])/100 +(SUM(QUANTITY*PRICE)*[VAT])/100)
					-((SUM(QUANTITY*PRICE*ITEMDISCOUNT/100))+((SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100)),
				EXPORT.TABLEID

		FROM EXPORT,EXPORTDETAIL,AREA,[TABLE]
		WHERE EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
				AND EXPORT.TABLEID= [TABLE].TABLEID
				AND [TABLE].AREAID = AREA.AREAID
				AND   DateAdd(day, datediff(day,0, Export.DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, Export.DATECREATE), 0) <=@TO
				AND ISPAYALL=@ISPAYALL
		GROUP BY EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, EXPORT.[SERVICE],EXPORT.VAT,EXPORT.TABLEDISCOUNT,EXPORT.VAT,EXPORT.TABLEID
		ORDER BY TOTAL DESC
	END


go
create PROC [dbo].[GETDETAILEXPORTBYTABLEID](@TABLEID INT)
AS
	BEGIN
		SELECT Export.KulQuantity,Export.MoneyExchangeByKul,EXPORT.ExportId,Export.DateCreate,Export.DatePaid,EXPORTDETAIL.ItemId,Quantity,EXPORTDETAIL.Price,ItemDiscount,IsItemPay,ItemName,Total=((QUANTITY*EXPORTDETAIL.PRICE)-(QUANTITY*EXPORTDETAIL.PRICE*ITEMDISCOUNT)/100),Vat,[Service],TableDiscount,EXPORTDETAIL.Note
		FROM EXPORT,EXPORTDETAIL,ITEM
		WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID 
			AND ITEM.ITEMID=EXPORTDETAIL.ITEMID
			AND TABLEID=@TABLEID
			AND EXPORT.ISPAYALL=0
	END

go



GO
-- start procedure  SYNTHESISREPORTTEST
create Procedure SYNTHESISREPORT(@FROM DATE, @TO DATE)
AS
BEGIN
declare @table  TABLE 
(Name nvarchar(100),
TotalTable int,
TotalMoney decimal
)

declare @TotalTable int
declare @TotalMoneyTable decimal

declare @TotalPaidTable int
declare @TotalMoneyPaidTable decimal

declare @TotalNotPaidTable int
declare @TotalNotMoneyPaidTable decimal

		set @TotalTable=(SELECT COUNT(EXPORTID)  FROM EXPORT WHERE DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO)

 set @TotalMoneyTable=(
SELECT SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)
			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))
					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO) 

 set @TotalPaidTable=(SELECT COUNT(EXPORTID) FROM EXPORT WHERE ISPAYALL=1 AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO)

 set @TotalMoneyPaidTable=(
SELECT SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)
			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))

					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO AND ISPAYALL =1)

 set @TotalNotPaidTable=(SELECT COUNT(EXPORTID) FROM EXPORT WHERE ISPAYALL=0 AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO)

 set @TotalNotMoneyPaidTable =(select SUM(((QUANTITY * PRICE) + (QUANTITY * PRICE)*VAT/100 + (QUANTITY * PRICE)*[SERVICE]/100)

			- ((QUANTITY * PRICE)- (  ((QUANTITY * PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * PRICE)*ITEMDISCOUNT)/100))

					 FROM EXPORTDETAIL,EXPORT WHERE EXPORT.EXPORTID=EXPORTDETAIL.EXPORTID AND DateAdd(day, datediff(day,0, DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, DATECREATE), 0) <=@TO AND ISPAYALL=0)




 
INSERT INTO @table values (N'Số bàn mở',@TotalTable,@TotalMoneyTable)
INSERT INTO @table values (N'Số bàn chưa thu',@TotalNotPaidTable,@TotalNotMoneyPaidTable)
INSERT INTO @table values (N'Số bàn thu',@TotalPaidTable,@TotalMoneyPaidTable)

SELECT * FROM @table
return
END
-- end procedure  SYNTHESISREPORTTEST



go
CREATE PROC REPORTBYEXPORDID(@FROM DATE, @TO DATE)
AS
	BEGIN
		SELECT 
				EXPORT.ExportId, AREA.AreaName, [TABLE].TableName,EXPORT.DateCreate, Total=SUM(QUANTITY*PRICE),
				[Service]=(SUM(QUANTITY*PRICE)*[SERVICE])/100,
				[Vat]=(SUM(QUANTITY*PRICE)*[VAT])/100,
				ItemDiscount=SUM(QUANTITY*PRICE*ITEMDISCOUNT/100*-1),
				TableDisCount= (SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100*-1,
				FinalTotal= (SUM(QUANTITY*PRICE)+(SUM(QUANTITY*PRICE)*[SERVICE])/100 +(SUM(QUANTITY*PRICE)*[VAT])/100)
					-((SUM(QUANTITY*PRICE*ITEMDISCOUNT/100))+((SUM((QUANTITY*PRICE) - (QUANTITY*PRICE*ITEMDISCOUNT/100))*TABLEDISCOUNT)/100))

		FROM EXPORT,EXPORTDETAIL,AREA,[TABLE]
		WHERE EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
				AND EXPORT.TABLEID= [TABLE].TABLEID
				AND [TABLE].AREAID = AREA.AREAID
				AND   DateAdd(day, datediff(day,0, export.DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, export.DATECREATE), 0) <=@TO
				and IsPayAll = 1
		GROUP BY EXPORT.EXPORTID, AREA.AREANAME, [TABLE].TABLENAME,EXPORT.DATECREATE, EXPORT.[SERVICE],EXPORT.VAT,EXPORT.TABLEDISCOUNT,EXPORT.VAT
		ORDER BY TOTAL DESC
	END
GO

CREATE  PROC REPORTBYITEM(@FROM DATE, @TO DATE)
AS
	BEGIN
		SELECT ITEM.ItemName,Quantity=SUM(QUANTITY),
		Total=SUM(((QUANTITY * EXPORTDETAIL.PRICE) + (QUANTITY * EXPORTDETAIL.PRICE)*VAT/100 + (QUANTITY * EXPORTDETAIL.PRICE)*[SERVICE]/100)

			- ((QUANTITY * EXPORTDETAIL.PRICE)- (  ((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100))
			
FROM ITEM,EXPORTDETAIL, EXPORT
WHERE ITEM.ITEMID = EXPORTDETAIL.ITEMID
AND EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
AND DateAdd(day, datediff(day,0, export.DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, export.DATECREATE), 0) <=@TO
and IsPayAll = 1
GROUP BY ITEMNAME
ORDER BY TOTAL DESC

	END
GO


CREATE PROC REPORTBYDATE(@FROM DATE, @TO DATE)
AS
	BEGIN
			SELECT [Date]=CAST( EXPORT.DATECREATE AS DATE),Quantity=SUM(QUANTITY),
		Total=SUM(((QUANTITY * EXPORTDETAIL.PRICE) + (QUANTITY * EXPORTDETAIL.PRICE)*VAT/100 + (QUANTITY * EXPORTDETAIL.PRICE)*[SERVICE]/100)

			- ((QUANTITY * EXPORTDETAIL.PRICE)- (  ((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100)  )*TABLEDISCOUNT/100 -  (((QUANTITY * EXPORTDETAIL.PRICE)*ITEMDISCOUNT)/100))
			
FROM ITEM,EXPORTDETAIL, EXPORT
WHERE ITEM.ITEMID = EXPORTDETAIL.ITEMID
AND EXPORT.EXPORTID = EXPORTDETAIL.EXPORTID
AND DateAdd(day, datediff(day,0, export.DATECREATE), 0) >=@FROM AND DateAdd(day, datediff(day,0, export.DATECREATE), 0) <=@TO
and IsPayAll = 1
GROUP BY CAST( EXPORT.DATECREATE AS DATE)
ORDER BY TOTAL DESC

	END
GO



--exec REPORTBYEXPORDID '2014/4/13','2014/4/13'